package cn.nwpu.eckel.Dao;

import static cn.nwpu.eckel.utility.DBConnection.close;
import static cn.nwpu.eckel.utility.DBConnection.getConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.nwpu.eckel.model.Forecast;
import cn.nwpu.eckel.model.ScheduleReceipt;


public class ScheduleReceiptDao {
	public static List<ScheduleReceipt> getScheduleReceiptsByMpsNo(int mpsNo){
		List<ScheduleReceipt> schedulereceipts=new ArrayList<ScheduleReceipt>();
		ScheduleReceipt tempScheduleReceipt;
		Connection con=getConnection();
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		String sql="select * from schedulereceipt_tb where mpsNo=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,mpsNo);
			rs=pstmt.executeQuery();
			while(rs.next()){
				tempScheduleReceipt=new ScheduleReceipt(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getString(5),rs.getDate(6));
				schedulereceipts.add(tempScheduleReceipt);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return schedulereceipts;
	}
	public static ScheduleReceipt deleteScheduleReceipt(int id){
		ScheduleReceipt schedulereceipt=ScheduleReceiptDao.getScheduleReceiptById(id);
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="delete from schedulereceipt_tb where id=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,id);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return schedulereceipt;
	}
	public static ScheduleReceipt getScheduleReceiptById(int id){
		ScheduleReceipt schedulereceipt=new ScheduleReceipt();
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String sql="select* from schedulereceiptr_tb where id=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,id);
			rs=pstmt.executeQuery();
			while(rs.next()){
				schedulereceipt.id=rs.getInt(1);
				schedulereceipt.mpsNo =rs.getInt(2);
				schedulereceipt.ts=rs.getInt(3);
				schedulereceipt.quantity=rs.getInt(4);
				schedulereceipt.people=rs.getString(5);
				schedulereceipt.buildTime=rs.getDate(6);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return schedulereceipt;
	}
	
    public static void insertScheduleReceipt(ScheduleReceipt schedulereceipt){
    	Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="insert into schedulereceipt_tb values(?,?,?,?,?,?)";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, schedulereceipt.id);
			pstmt.setInt(2, schedulereceipt.mpsNo);
			pstmt.setInt(3, schedulereceipt.ts);
			pstmt.setInt(4, schedulereceipt.quantity);
			pstmt.setString(5, schedulereceipt.people);
			pstmt.setDate(6, schedulereceipt.buildTime);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
	}
    public static ScheduleReceipt roll(int mpsNo){
    	ScheduleReceipt s=new ScheduleReceipt();
		Connection con=getConnection();
		PreparedStatement pstmt_1=null;
		PreparedStatement pstmt_2=null;
		PreparedStatement pstmt_3=null;
		ResultSet rs=null;
		String sql_1="select * from schedulereceipt_tb where mpsNo=? and ts=0";
		String sql_2="delete from schedulereceipt_tb where mpsNo=? and ts=0";
		String sql_3="update schedulereceipt_tb set ts=ts-1 where mpsNo=?";
		try{
			pstmt_1=con.prepareStatement(sql_1);
			pstmt_2=con.prepareStatement(sql_2);
			pstmt_3=con.prepareStatement(sql_3);
			
			pstmt_1.setInt(1,mpsNo);
			rs=pstmt_1.executeQuery();
			
			pstmt_2.setInt(1,mpsNo);
			pstmt_2.executeUpdate();
			
			pstmt_3.setInt(1,mpsNo);
			pstmt_3.executeUpdate();
			
			while(rs.next()){
				s.id=rs.getInt(1);
				s.mpsNo=rs.getInt(2);
				s.ts=rs.getInt(3);
				s.quantity=rs.getInt(4);
				s.people=rs.getString(5);
				s.buildTime=rs.getDate(6);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt_1);
			close(pstmt_2);
			close(con);
		}
		return s;
	}
	public static ScheduleReceipt alterScheduleReceipt(ScheduleReceipt newSr){
		ScheduleReceipt oldSr=ScheduleReceiptDao.getScheduleReceiptById(newSr.id);
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="update schedulereceipt_tb set quantity=?,people=?,buildTime=? where mpsNo=? and ts=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, newSr.quantity);
			pstmt.setString(2,newSr.people);
			pstmt.setDate(3,newSr.buildTime);
			pstmt.setInt(4, newSr.mpsNo);
			pstmt.setInt(5, newSr.ts);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return oldSr;
	}
	
}
